import 'package:flutterdemo/models/distribution.dart';
import 'package:sqflite/sqflite.dart';

import 'sqflite_helper.dart';

const String _staffNo = 'STAFF_NO';
const String _staffName = 'STAFF_NAME';
const String _score = 'SCORE';
const String _detectTime = 'DETECT_TIME';

class FaceModel {
  int staffNo;
  String staffName;
  double score;
  String detectTime;

  FaceModel();

  Map<String, dynamic> toMap() {
    Map<String, dynamic> map = {
      _staffNo: staffNo,
      _staffName: staffName,
      _score: score,
      _detectTime: detectTime,
    };
    return map;
  }

  FaceModel.fromMap(Map<String, dynamic> map) {
    staffNo = map[_staffNo];
    staffName = map[_staffName];
    score = map[_score];
    detectTime = map[_detectTime];
  }
}

class FaceModelProvider {
  static FaceModelProvider _instance;

  factory FaceModelProvider() => _getInstance();

  static FaceModelProvider _getInstance() {
    if (_instance == null) {
      _instance = FaceModelProvider._();
    }
    return _instance;
  }

  //私有构造
  FaceModelProvider._();

  static const String _table = 'FACE_MODEL';
  static const String _databaseName = "iodevface.db";
  String createSql = 'CREATE TABLE $_table (_id INTEGER PRIMARY KEY,'
      ' $_staffNo INTEGER, '
      ' $_staffName TEXT, '
      ' $_score REAL, '
      ' $_detectTime TEXT)';

  insert(FaceModel faceModel) async {
    var _database = await SqfliteHelper().initDatabase(_databaseName);
    _database.insert(_table, faceModel.toMap());
  }

  Future inserts(List<FaceModel> faceModelList) async {
    var _database = await SqfliteHelper().db;
    Batch batch = _database.batch();
    faceModelList.forEach((element) {
      batch.insert(_table, element.toMap());
    });
    return await batch.commit();
  }

  Future<List<FaceModel>> queryAll() async {
    var _database = await SqfliteHelper().initDatabase(_databaseName);
    List<Map> maps = await _database.query(_table);
    List<FaceModel> list = [];
    maps.forEach((element) {
      list.add(FaceModel.fromMap(element));
    });
    return list;
  }

  ///数据合并
  merge() async {
    var _databaseA = await SqfliteHelper().initDatabase('iodevface17L.db');
    List<Map> maps = await _databaseA.query(_table);
    List<FaceModel> list = [];
    maps.forEach((element) {
      list.add(FaceModel.fromMap(element));
      print('face=$element');
      insert(FaceModel.fromMap(element));
    });
    // await inserts(list);
  }

  ///查询平均分值
  Future<List<FaceModel>> queryAvg() async {
    var _database = await SqfliteHelper().initDatabase(_databaseName);
    var sql =
        'select staff_name,avg(score) as SCORE from face_model where score>70 group by staff_name order by avg(score) ';
    List<Map> maps = await _database.rawQuery(sql);
    List<FaceModel> list = [];
    maps.forEach((element) {
      list.add(FaceModel.fromMap(element));
    });
    return list;
  }

  ///分值递增时区间个数分布
  Future<List<Distribution>> queryDistribution() async {
    List<Distribution> list = new List();
    var _database = await SqfliteHelper().initDatabase(_databaseName);
    int _score = 70; //起始分值
    while (_score < 100) {
      var sql =
          'select count(*) as yAxis from $_table where score>=$_score and score < ${_score + 1}';
      List<Map> maps = await _database.rawQuery(sql);
      if (maps.length == 1) {
        int count = maps[0]['yAxis'];
        var distribution = Distribution();
        distribution.xAxis = '$_score';
        distribution.yAxis = count;
        list.add(distribution);
        print('score=$_score,count=$count');
      }
      _score++;
    }
    return list;
  }

  Future clear() async {
    var _database = await SqfliteHelper().initDatabase(_databaseName);
    await _database.delete(_table);
  }
}
